package atguigu;

import java.io.File;

import cn.hutool.core.io.FileUtil;

public class Insert_sql {

	public static void main(String[] args) {
//		buildSql();
		
		buildSql1();
//		buildsql2();
	}
	
	public static void buildSql() {
		String sqlString = " INSERT INTO T_ActualTestValue_All_NEW_6 "+
				" (oid, createTime, editTime, objectNumber, objectName, description, mpmType, buId, "+ 
				" plantId, scadaFormId, testItemName, testItemMasterId, testFormItemMasterId, "+ 
				" isAlarm, actualTime, equipmentName, equipmentNumber, exceptionNumber, "+ 
				" testFormNumber, sampleModel, sampleNumber, consigner, "+ 
				" consignDpt, testFormType, testCondition, varStr1, "+ 
				" varStr2, varStr3, varStr4, varStr5, varStr6, varStr7, varStr8, "+ 
				" varStr9, varStr10, varStr11, varStr12, varStr13, varStr14) "+
				" select "+
				" t.oid, t.createTime, t.editTime, t.objectNumber, t.objectName, t.description, t.mpmType, t.buId, "+ 
				" t.plantId, t.scadaFormId, t.testItemName, t.testItemMasterId, t.testFormItemMasterId, "+ 
				" t.isAlarm, t.actualTime, t.equipmentName, t.equipmentNumber, t.exceptionNumber, "+ 
				" ttj.testFormNumber, ttj.sampleModel, ttj.sampleNumber, ttj.consigner, "+ 
				" ttj.consignDpt, ttj.testFormType, ttj.testCondition, t.varStr1, "+ 
				" t.varStr2, t.varStr3, t.varStr4, t.varStr5, t.varStr6, t.varStr7, t.varStr8, "+ 
				" t.varStr9, t.varStr10, t.varStr11, t.varStr12, t.varStr13, t.varStr14 "+
				" from T_ActualTestValue_All_NEW_5 t, t_testformitem_jd ttj "+
				" where t.actualTime > '2022-06-01' "+
				" and ttj.isLatestVersion = 1 and ttj.isLatestIteration = 1 "+ 
				" and ttj.masterId = t.testFormItemMasterId "+ 
				" LIMIT xxxx,10000 ;";
		
		for (int i = 0; i < 100; i++) {
			int j = i * 10000;
			String replaceAll = sqlString.replaceAll("xxxx", j+"");
			FileUtil.appendString(replaceAll+"\r\n", new File("d:/insert_sql.sql"), "utf-8");
		}
	}
	
	
	public static void buildSql1() {
		String sqlString = "insert into midea_lims_new.actualtestvalue\r\n" + 
				"(oid,createtime,edittime,testitemname,testitemmasterid,testFormItemName,testitemnumber,testcondition,alarmformid,\r\n" +
				"testformitemmasterid,isalarm,actualtime,equipmentname,equipmenttwname,equipmentnumber,exceptionnumber,\r\n" + 
				"testformnumber,samplemodel,samplenumber,consigner,consigndpt,exceptiondescribe,examineopinion,varstr1,\r\n" + 
				"varstr2,varstr3,varstr4,varstr5,varstr6,varstr7,varstr8,varstr9,varstr10,varstr11,varstr12,varstr13,\r\n" + 
				"varstr14,buid)\r\n" + 
				"select p.oid                  as oid,\r\n" + 
				"       p.createtime           as createtime,\r\n" + 
				"       p.edittime             as edittime,\r\n" + 
				"       p.testitemname         as testitemname,\r\n" + 
				"       p.testitemmasterid     as testitemmasterid,\r\n" +
				" (select tta.objectname\n" +
				"        from midea_lims.t_testformitem_jd ttj ,midea_lims.t_testitem_all tta\n" +
				"        where ttj.masterId = p.testformitemmasterid\n" +
				"          and ttj.testItemMasterId = tta.masterId\n" +
				"          and ttj.isLatestIteration = 1 and ttj.isLatestVersion = 1\n" +
				"          and tta.isLatestIteration = 1 and tta.isLatestVersion = 1)           as testFormItemName,"+
				"       (select tta.objectNumber\r\n" + 
				"       from midea_lims.t_testformitem_jd ttj ,midea_lims.t_testitem_all tta\r\n" + 
				"        where ttj.masterId = p.testformitemmasterid\r\n" + 
				"        and ttj.testItemMasterId = tta.masterId\r\n" + 
				"        and ttj.isLatestIteration = 1 and ttj.isLatestVersion = 1\r\n" + 
				"        and tta.isLatestIteration = 1 and tta.isLatestVersion = 1) as testitemnumber,\r\n" + 
				"       p.testcondition        as testcondition,\r\n" + 
				"       (select oid from midea_lims.t_alarmform_jd taj\r\n" + 
				"       where taj.testFormItemMasterId = p.testformitemmasterid\r\n" + 
				"       and taj.isLatestIteration=1\r\n" + 
				"       and taj.isLatestVersion = 1 limit 0,1) as alarmformid,\r\n" + 
				"       p.testformitemmasterid as testformitemmasterid,\r\n" + 
				"       p.isalarm              as isalarm,\r\n" + 
				"       p.actualtime           as actualtime,\r\n" + 
				"       p.equipmentname        as equipmentname,\r\n" + 
				"       p.equipmentNumber      as equipmenttwname,\r\n" + 
				"       p.objectName           as equipmentnumber,\r\n" + 
				"       p.exceptionnumber      as exceptionnumber,\r\n" + 
				"       p.testformnumber       as testformnumber,\r\n" + 
				"       p.samplemodel          as samplemodel,\r\n" + 
				"       p.samplenumber         as samplenumber,\r\n" + 
				"       p.consigner            as consigner,\r\n" + 
				"       p.consigndpt           as consigndpt,\r\n" + 
				"       p.description          as exceptiondescribe,\r\n" + 
				"       ''                     as examineopinion,\r\n" + 
				"       p.varstr1              as varstr1,\r\n" + 
				"       p.varstr2              as varstr2,\r\n" + 
				"       p.varstr3              as varstr3,\r\n" + 
				"       p.varstr4              as varstr4,\r\n" + 
				"       p.varstr5              as varstr5,\r\n" + 
				"       p.varstr6              as varstr6,\r\n" + 
				"       p.varstr7              as varstr7,\r\n" + 
				"       p.varstr8              as varstr8,\r\n" + 
				"       p.varstr9              as varstr9,\r\n" + 
				"       p.varstr10             as varstr10,\r\n" + 
				"       p.varstr11             as varstr11,\r\n" + 
				"       p.varstr12             as varstr12,\r\n" + 
				"       p.varstr13             as varstr13,\r\n" + 
				"       p.varstr14             as varstr14,\r\n" + 
				"       p.buid                 as buid\r\n" + 
				" from midea_lims.t_actualtestvalue_all_new_6 p limit xxxx,100000;";
		
		for (int i = 0; i < 10; i++) {
			int j = i * 100000;
			String replaceAll = sqlString.replaceAll("xxxx", j+"");
			FileUtil.appendString(replaceAll+"\r\n", new File("d:/insert_sql.sql"), "utf-8");
		}
	}
	
	public static void buildsql2() {
		String sqlString = "insert into midea_lims_new.testexceptionform\r\n" + 
				"(oid,createtime,edittime,securitylevel,objectnumber,objectname,creatorname,creatorfullname,\r\n" + 
				"editorname,editorfullname,description,objectdefid,domainid,containerid,folderid,status,buid,plantid,\r\n" + 
				"versionsort,masterid,branchid,viewid,islatestversion,islatestiteration,isreleased,version,\r\n" + 
				"iteration,lockstatus,lockedby,lockedbyfullname,versionmessage,alarmname,alarmvalue,actualoid,\r\n" + 
				"alarmoid,actualvalue,completetime,handler,exceptiondescribe,examineopinion,questiontype,conclusion,\r\n" + 
				"questiondescribes,extype,receivedate,handerdate,handertime,receivetime,alarmvalueoid)\r\n" + 
				"select p.oid                            as oid,\r\n" + 
				"       p.createtime                     as createtime,\r\n" + 
				"       p.edittime                       as edittime,\r\n" + 
				"       0                                as securitylevel,\r\n" + 
				"       p.objectnumber                   as objectnumber,\r\n" + 
				"       p.objectnumber                   as objectname,\r\n" + 
				"       p.creatorname                    as creatorname,\r\n" + 
				"       p.creatorfullname                as creatorfullname,\r\n" + 
				"       p.editorname                     as editorname,\r\n" + 
				"       p.editorfullname                 as editorfullname,\r\n" + 
				"       p.description                    as description,\r\n" + 
				"       0                                as objectdefid,\r\n" + 
				"       0                                as domainid,\r\n" + 
				"       0                                as containerid,\r\n" + 
				"       0                                as folderid,\r\n" + 
				"       p.lifecycle                      as status,\r\n" + 
				"       p.buid                           as buid,\r\n" + 
				"       -999                             as plantid,\r\n" + 
				"       p.versionsort                    as versionsort,\r\n" + 
				"       p.masterid                       as masterid,\r\n" + 
				"       p.branchid                       as branchid,\r\n" + 
				"       20288                            as viewid,\r\n" + 
				"       p.islatestversion                as islatestversion,\r\n" + 
				"       p.islatestiteration              as islatestiteration,\r\n" + 
				"       p.isReleasedObject               as isreleased,\r\n" + 
				"       p.version                        as version,\r\n" + 
				"       p.iteration                      as iteration,\r\n" + 
				"       'ci'                             as lockstatus,\r\n" + 
				"       p.lockedby                       as lockedby,\r\n" + 
				"       p.lockedbyfullname               as lockedbyfullname,\r\n" + 
				"       p.version                        as versionmessage,\r\n" + 
				"       p.alarmname                      as alarmname,\r\n" + 
				"       p.alarmvalue                     as alarmvalue,\r\n" + 
				"       p.actualoid                      as actualoid,\r\n" + 
				"       (select t3.oid\r\n" + 
				"        from midea_lims.t_alarmform_jd t3,\r\n" + 
				"             midea_lims.t_alarmvalue_all t4\r\n" + 
				"        where t3.masterId = t4.alarmFormMastId\r\n" + 
				"          and t4.oid = p.alarmOid\r\n" + 
				"          and t3.isLatestVersion = 1\r\n" + 
				"          and t3.isLatestIteration = 1\r\n" + 
				"        order by t3.oid desc\r\n" + 
				"        limit 0,1)                      as alarmoid,\r\n" + 
				"       p.actualvalue                    as actualvalue,\r\n" + 
				"       p.completetime                   as completetime,\r\n" + 
				"       p.handler                        as handler,\r\n" + 
				"       p.exceptiondescribe              as exceptiondescribe,\r\n" + 
				"       p.examineopinion                 as examineopinion,\r\n" + 
				"       p.questiontype                   as questiontype,\r\n" + 
				"       p.conclusion                     as conclusion,\r\n" + 
				"       p.questiondescribes              as questiondescribes,\r\n" + 
				"       p.extype                         as extype,\r\n" + 
				"       null                             as receivedate,\r\n" + 
				"       null                             as handerdate,\r\n" + 
				"       0                                as handertime,\r\n" + 
				"       0                                as receivetime,\r\n" + 
				"       p.alarmOid                       as alarmvalueoid\r\n" + 
				"from midea_lims.t_testexceptionform_jd p limit xxxx,10000;";
		
		for (int i = 0; i < 300; i++) {
			int j = i * 10000;
			String replaceAll = sqlString.replaceAll("xxxx", j+"");
			FileUtil.appendString(replaceAll+"\r\n", new File("d:/insert_sql2.sql"), "utf-8");
		}
	}
	
	public static void buildSql3() {
		String sqlString= "";
	}
}
